<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf-8">
<TITLE>Maptrick 2.0</TITLE>
<script src="breakout.js" language="JavaScript" type="text/javascript">
</script>
</HEAD>
<BODY onload="breakout_of_frame()">

<STYLE>
TABLE {
	border: 1px solid black;
	border-spacing: 0px;
	border-collapse: collapse;
}

table.sups {
	border: none
}


TH.medi {
	font-family: sans-serif;
}

TH.info {
	font-size: 12;
	font-family: sans-serif;
	background-color: #EEFFEE;
}

TD.dates {
	font-size: 12;
	font-family: sans-serif;
}

TD.graph {
	text-align: center;
	font-family: sans-serif;
	font-size: 14;
}

TD.medi {
	text-align: center;
	font-family: sans-serif;
	font-size: 18;
}

TD.mini {
	text-align: center;
	font-size: 8;
	font-family: sans-serif;
}

TD.info {
	font-size: 12;
	font-family: sans-serif;
	background-color: #EEFFEE;
}

TD.num {
	text-align: right;
}

DIV.kader {
	border: 1px black dotted;
	padding: 10px;
}

</STYLE>

<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set("memory_limit","100M");

include "functions.inc.php";
include "dropdowns.inc.php";
include "tables.inc.php";
require_once("track.inc.php");
require_once("database.inc.php");
require_once("ad.inc.php");

$lastupdate = getLastUpdate();
$updates = getUpdates();
$allupdates = false;
if (isset($_GET['allUpdates']) && $_GET['allUpdates'] > 0) {
	$allupdates = true;
	$updates = getAllUpdates();
}
$action = 0;
if (isset($_GET['action']) && $_GET['action'] == 1) $action = 1;

//hit();

$dbh = connect_db();

echo '<FORM NAME="maptrick" ACTION="maptrick.php" METHOD="get">';

$weather = array("Rain", "Overcast", "Partially Cloudy", "Sunny");
$englishleaguenames = getAsocArr("leagues_weekly", "LeagueID", "EnglishName", $dbh);
$regionnames = getAsocArr("regions", "RegionID", "RegionName", $dbh);
$regionleagues = getAsocArr("regions", "RegionID", "LeagueID", $dbh);

#printzonedropdown($_GET['zoneid'], $dbh);
printleaguedropdown($_GET['leagueid'], $_GET['zoneid'], "Pick country...", $dbh);
if ($_GET['regionid']) {
	printregiondropdown($_GET['regionid'],$_GET['leagueid'], $dbh);
}

$actions = array(
	0 => "Show Bots",
	1 => "Show Supporters"
);
printgenericdropdown("action", $_GET['action'], "", $actions);

echo "\t<INPUT TYPE=\"submit\" VALUE=\"Go!\"/></FORM>\n";
echo "<B><FONT color=\"red\">Select a league above, or select a region to only show those leagues in the list.</FONT></B><br/><br/>\n\n";
?>
<br />
Maptrick is a webpage containing statistical information about countries in the on-line game, <a href="http://www.hattrick.org/">Hattrick</a>. It is currently a work in progress. What you see here is a basic representation of the data currently available in the database. I use this data to develop Maptrick further into its projected final form: a clickable map of the world, showing colorings, graphs and statistics for the regions you click. Still, the page at hand provides most of the data the final version will display, so it is provided for reference purposes. If you have an idea for a new statistic to draw on this page, drop me a line; my HT username is CHPP-ste1n, e-mail is ste1n (put an "@" here) xs4all.nl. Enjoy the webpage (<i>if you can't see it yet, it's probably still loading</i>)!<br /><br /><br />

<?php
display_ad("maptrick_top");

if (is_numeric($_GET['leagueid']) && $_GET['leagueid'] > 0) {
	$country = getleaguearray($_GET['leagueid'], $dbh);
	$snapdir = "wosnaps";
	if ($_GET['action'] == 1) {
		$snapdir = "supsnaps";
	}
	#(not so) temp fix until block size becomes normal again
	$prefix = "http://maptrick.nl.eu.org/~laszlo";
	$snapdir = "$prefix/$snapdir";
	#end fix
	echo '<DIV class="kader">';
	echo "<b>League details: {$country['LeagueName']}</b> <IMG SRC=\"http://static.alltid.org/images/{$_GET['leagueid']}flag.gif\" BORDER=\"1\"/><br/>\n";
	echo "Season: {$country['Season']}<br/>\n";
	echo "Number of regions: ".count($country['Regions'])."<br/>\n";
	echo "<br/>\n";
	echo "Total capacity: {$country['NumberOfLevels']} levels -> ".levels2teams($country['NumberOfLevels'])." teams (including bots)<br/>\n";
	$capacity = round((($country[ActiveUsers])/(levels2teams($country[NumberOfLevels])) * 100),1);
	echo "Number of active users: $country[ActiveUsers] ($capacity% of total capacity)<br/>\n";
	$waiting = round((($country[WaitingUsers])/(levels2teams($country[NumberOfLevels])) * 100),1);
	echo "Number of waiting users: $country[WaitingUsers] ($waiting% of total capacity)<br/>\n";
	$total_interested = $capacity + $waiting;
	echo "Total number of interested people is $total_interested% of current capacity.<br/>\n";
	if ($total_interested > 100) echo "<font color=\"red\">candidate for expansion?</font><br/>\n";
	$active_per_region = round($country['ActiveUsers'] / count($country['Regions']), 2);
	echo "Active users per region: $active_per_region<br/>\n";
	
	echo "<br/>\n";
	
	echo "History of number of users:<BR/>\n";
	echo "<IMG ALT=\"History of number of Active / Total users\" SRC=\"graphs/{$_GET['leagueid']}.png\"><BR/>\n<BR/>\n";
	echo "<IMG ALT=\"History of number of Waiting users\" SRC=\"graphs/{$_GET['leagueid']}-w.png\"><BR/>\n<BR/>\n";
	echo "<IMG ALT=\"Historical waiting times when applying\" SRC=\"graphs/{$_GET['leagueid']}-t.png\"><BR/>\n<BR/>\n";
	echo "<IMG ALT=\"Historical training type distribution\" SRC=\"graphs/t{$_GET['leagueid']}.png\"><BR/>\n<BR/>\n";
	echo "A page with all graphs of each country is <A HREF=\"allgraphs.php\">here</A>.<BR/>\n<BR/>\n<BR/>\n";
	
	echo "<B>Regions in {$country['LeagueName']}</B> - regions with much higher ID's than the others were added later\n";
	$pievars = "title=" . urlencode(utf8_decode("Regions in {$country['LeagueName']}"));
//	$pievars = "title=Regions in {$country['LeagueName']}";
	$auir = array(); #Active Users In Region
	$tauir = 0; #Total Active Users In Regions
	$query = mysql_query("SELECT regionid, count(*) num FROM `teams_$lastupdate` WHERE leagueid = {$_GET['leagueid']} AND userid > 0 GROUP BY regionid",$dbh);
	while ($row = mysql_fetch_array($query)) {
		$auir[$row['regionid']] = $row['num'];
		$tauir += $row['num'];
	}
	
	$query = mysql_query("SELECT SUM(NumberOfOnline) FROM regions WHERE leagueid = {$_GET['leagueid']}",$dbh);
	$total_online = mysql_result($query, 0);

	$regions = mysql_query("SELECT * from regions where leagueid = {$_GET['leagueid']} order by numberofusers desc",$dbh);
	echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Region (ID#)</B></TD><TD CLASS=\"info\"><B>Total teams</B><td class=\"info\"><B>Active teams*</B></TD><TD CLASS=\"info\"><B>On-line users</B></TD><TD CLASS=\"info\"><B>Weather today</B></TD><TD CLASS=\"info\"><B>Forecast</B></TD></TR>\n";
	while ($region = mysql_fetch_array($regions)) {
		$users_perc = round((($region['NumberOfUsers'] / levels2teams($country[NumberOfLevels]))* 100),1);
		#$pievars = $pievars . "&slice[]={$region['NumberOfUsers']}&itemName[]=" . $region['RegionID'];
		if ($auir[$region['RegionID']] > 0) {
			$pievars = $pievars . "&slice[]={$auir[$region['RegionID']]}&itemName[]=" . $region['RegionID'];
		}
		if ($tauir > 0) {
			$auir_perc = round((($auir[$region['RegionID']] / $tauir) * 100),1);
		} else {
			$auir_perc = 0;
		}

		if ($total_online > 0) {
			$online_perc = round((($region['NumberOfOnline'] / $total_online)* 100),1);
		} else {
			$online_perc = 0;
		}

		echo "\t<TR><TD CLASS=\"info\">{$region['RegionName']} ({$region['RegionID']})</TD><TD CLASS=\"info\">{$region['NumberOfUsers']} ($users_perc%)</TD><td class=\"info\">{$auir[$region['RegionID']]} ($auir_perc%)<TD CLASS=\"info\">{$region['NumberOfOnline']} ($online_perc%)</TD><TD CLASS=\"info\">{$weather[$region['WeatherID']]}</TD><TD CLASS=\"info\">{$weather[$region['TomorrowWeatherID']]}</TD></TR>\n";
	}
	echo "</TABLE><br />*: on $lastupdate<br /><br />\n";
	echo "<IMG SRC=\"pieChart.php?$pievars\"/><BR/>";
		
/*		
	if (isset ($_GET['regionid']) && $_GET['regionid'] > 0) {		
		$regionid = $_GET['regionid'];
		$region = get_lastscandata($country['Regions'][$regionid]);
		echo "<b>Region details: {$region['RegionName']}</b><br/>\n";
		echo "Number of teams (including bots): " . $region['NumberOfUsers'] . "<br/>\n";
		if ($total_online > 0)
			$region_part = round((($region['NumberOfOnline'] / $total_online)* 100),1);
		echo "On-line users: $region[NumberOfOnline] ($region_part% of country total)<br/>\n";
		echo "Weather today: " . $weather[$region['WeatherID']] . "<br/>\n";
		echo "Forecast: " . $weather[$region['TomorrowWeatherID']] . "<br/>\n";
		
	}
	*/
	echo '</DIV>';
	echo "<br/>\n";


	echo '<DIV class="kader">';
	if ($_GET['action'] == 1) {
		echo "<B>Distribution of HT supporters; latest update: $lastupdate</B><BR/>\n";
	} else {
		echo "<B>Distribution of ownerless teams (bots); latest update: $lastupdate</B><BR/>\n";
	}

	$leagueid = $_GET['leagueid'];

	echo "<FONT SIZE=\"6\">This page shows all updates after the April 2006 league reform. It will eventually be merged with the <a href=\"maptrick_old.php?leagueid=$leagueid\">old updates</a>, when I have time.</FONT><BR/>\n";
	echo "<I>(Due to a Firefox bug, the table below may be missing borders. Also, the images may refuse to swap when pointing at a diferent date. If any of this happens, reloading the page (F5) helps most of the time. You could use IE, but that has other disadvantages... Also, if your ISP is Earthlink, be sure to turn off your accelerator; it causes problems with images loading (thanks jimrtex!))</I><BR/><BR/>\n\n";
	if ($_GET['action'] == 1) {
		echo "<B>Explanation:</B> below you can see a graphical representation of the number of HT supporters in each division. The colors in the square and the histogram correspond to the number of supporters; grey means that there are no supporters, black means all 8 teams are supporters, yellow means 4 supporters, etc.<BR/><BR/>\n\n";
	} else {
		echo "<B>Explanation:</B> below you can see a graphical representation of the number of ownerless teams in each division. The colors in the square and the histogram correspond to the number of active users; grey means that all teams are bots, black means all 8 teams are active, yellow means 4 bots, etc.<BR/><BR/>\n\n";
	}

	$series = array();
	$wos = array();
	$blocksize = 8;
	
	$query = mysql_query("SELECT * FROM series_new WHERE CountryID = $leagueid ORDER BY level ASC, seriesid ASC", $dbh);

	while ($row = mysql_fetch_array($query)) {
		$series[$row['level']][$row['SeriesID']] = $row['SeriesName'];
	}

	$numact = array();
	$totwos = array();
	$avg = array();
	$height = array();
	$perc = array();
	$maxcat = array();
	foreach (array_keys($series) as $level) {
		$maxcat[$level] = 0;
	}
	
	ob_flush();
	flush();

	foreach ($updates as $update) {
		$condition = "userid != 0";
		if ($_GET['action'] == 1) {
			$condition = "hassupporter = 1";
		}
		$query = mysql_query("SELECT seriesid, count(*) num FROM `teams_$update` WHERE $condition AND leagueid = $leagueid GROUP BY seriesid", $dbh);
		while ($row = mysql_fetch_array($query)) {
			$wos[$update][$row['seriesid']] = $row['num'];
		}
		foreach (array_keys($series) as $level) {
			foreach (array_keys($series[$level]) as $serie) {
				if (!$wos[$update][$serie] > 0) $wos[$update][$serie] = 0;
				$numact[$update][$level][$wos[$update][$serie]]++;
			}
			$totwos[$update][$level] = 0;
			for ($j = 0; $j <= 8; $j++) {
				$fact = 8-$j;
				if ($_GET['action'] == 1) {
					$fact = $j;
				}
				$totwos[$update][$level] += ($fact)*$numact[$update][$level][$j];
				if ($numact[$update][$level][$j] > $maxcat[$level]) $maxcat[$level] = $numact[$update][$level][$j];
			}
			$avg[$update][$level] = round($totwos[$update][$level]/count($series[$level]),2);
		}
	}
	foreach ($updates as $update) {
		foreach (array_keys($series) as $level) {
			for ($j = 0; $j <=8; $j++) {
				if (!isset($numact[$update][$level][$j])) $numact[$update][$level][$j] = 0;
				$height[$update][$level][$j] = round((($numact[$update][$level][$j] / $maxcat[$level]) * 180) + 2);
				$perc[$update][$level][$j] = round(100*($numact[$update][$level][$j] / count($series[$level])),1);
			}
		}
	}

	$age = array();
	$curseason = 31;
	
	$query = mysql_query("SELECT * FROM seasons WHERE leagueid = $leagueid", $dbh);
	while ($row = mysql_fetch_array($query)) {
		for ($i = 29; $i <= $curseason; $i++) {
			if (!isset($age[$row["s$i"]])) $age[$row["s$i"]] = $curseason - $i;
		}
	}
	
	$levels = array_keys($age);
	$hi = array_pop($levels);
	$lo = array_shift($levels);
	for ($i = $hi; $i > $lo; $i--) {
		if(!isset($age[$i-1])) $age[$i-1] = $age[$i];
	}
	
	echo "<SCRIPT TYPE=\"text/javascript\" LANGUAGE=\"JavaScript\">\n";
	echo "function changeimages(update) {\n";
	echo "\tvar updates = new Array()\n";
	for ($i = 0; $i < count($updates); $i++) {
		echo "\tupdates[$i] = \"{$updates[$i]}\"\n";
	}
	echo "\tnumact = new Array()\n";
	echo "\ttotwos = new Array()\n";
	echo "\tavg = new Array()\n";
	echo "\theight = new Array()\n";
	echo "\tperc = new Array()\n\n";

	for ($i = 0; $i < count($updates); $i++) {

		echo "\tnumact[$i] = new Array()\n";
	        echo "\ttotwos[$i] = new Array()\n";
	        echo "\tavg[$i] = new Array()\n";
	        echo "\theight[$i] = new Array()\n";
	        echo "\tperc[$i] = new Array()\n";
	
		for ($j = 1; $j <= $hi; $j++) {
			echo "totwos[$i][$j] = {$totwos[$updates[$i]][$j]}\n";
			echo "avg[$i][$j] = {$avg[$updates[$i]][$j]}\n";
			echo "numact[$i][$j] = new Array()\n";
			echo "height[$i][$j] = new Array()\n";
			echo "perc[$i][$j] = new Array()\n";
			for ($k = 0; $k <= 8; $k++) {
				echo "numact[$i][$j][$k] = {$numact[$updates[$i]][$j][$k]}\n";
				echo "height[$i][$j][$k] = {$height[$updates[$i]][$j][$k]}\n";
				echo "perc[$i][$j][$k] = {$perc[$updates[$i]][$j][$k]}\n";
			}
		}
	}	

	echo "\tif(navigator.appVersion.charAt(0)>=3){\n";
	for ($l = 1; $l <= $hi; $l++) {
		echo "\t\tdocument.images[\"l$l\"].src=\"$snapdir/{$leagueid}_{$l}_\"+updates[update]+\".png\"\n";
		echo "\t\ttotavg$l.innerHTML = totwos[update][$l]+\" (\"+avg[update][$l]+\")\"\n";
		for ($k = 0; $k <=8; $k++) {
			echo "\t\t\tact{$l}_{$k}.innerHTML = numact[update][$l][$k]\n";
			echo "\t\t\tperc{$l}_{$k}.innerHTML = perc[update][$l][$k]+\"%\"\n";
			echo "\t\t\tdocument.images[\"bar{$l}_{$k}\"].height = height[update][$l][$k]\n";
		}
	}
	echo "\t}\n";
	echo "}\n\n";
	echo "</SCRIPT>\n";

#	echo "<TABLE>\n";
#	echo "<TR><TD CLASS=\"info\" COLSPAN=\"2\">Color code</TD></TR>";
#	echo "<TR><TD CLASS=\"info\"><IMG SRC=\"0-8/0.png\" HEIGHT=\"$blocksize\" WIDTH=\"$blocksize\"/></TD><TD CLASS=\"info\">No active users</TD></TR>\n";
#	echo "<TR><TD CLASS=\"info\"><IMG SRC=\"0-8/1.png\" HEIGHT=\"$blocksize\" WIDTH=\"$blocksize\"/></TD><TD CLASS=\"info\">1 active user</TD></TR>\n";
#	for ($i = 2; $i <= 8 ;$i++) {
#		echo "<TR><TD CLASS=\"info\"><IMG SRC=\"0-8/{$i}.png\" HEIGHT=\"$blocksize\" WIDTH=\"$blocksize\"/></TD><TD CLASS=\"info\">$i active users</TD></TR>\n";
#	}
#	echo "</TABLE>\n<BR/><BR/>\n";

	echo "<TABLE BORDER=\"1\">\n\t<TR><TH CLASS=\"medi\">Level<TH CLASS=\"medi\">Age<TH CLASS=\"medi\">Distribution<TH CLASS=\"medi\">Tot. (Avg.)<TH CLASS=\"medi\" COLSPAN=\"9\">";
	if ($_GET['action'] == 1) {
		echo "Supporters per league\n";
	} else {
		echo "Active users per league\n";
	}
	foreach (array_keys($series) as $level) {
		echo "\t<TR><TD CLASS=\"medi\" ROWSPAN=\"3\">$level<TD CLASS=\"medi\" ROWSPAN=\"3\">{$age[$level]}";
		echo "<TD CLASS=\"medi\" ROWSPAN=\"3\"><CENTER><IMG SRC=\"$snapdir/{$leagueid}_{$level}_$lastupdate.png\"/ ID=\"l$level\"><BR/>\n";
		echo "</CENTER>\n";
		echo "<TD CLASS=\"medi\" ROWSPAN=\"3\" ID=\"totavg$level\">{$totwos[$lastupdate][$level]} ({$avg[$lastupdate][$level]})";
		for($j = 0; $j <=8; $j++) {
			echo "<TD CLASS=\"medi\">$j";
		}
		echo "<TR>";
		for($j = 0; $j <=8; $j++) {
			echo "<TD CLASS=\"mini\" VALIGN=\"bottom\"><DIV ID=\"act{$level}_$j\">{$numact[$lastupdate][$level][$j]}</DIV><BR/><IMG SRC=\"0-8/$j.png\" WIDTH=\"21\" HEIGHT=\"{$height[$lastupdate][$level][$j]}\" ID=\"bar{$level}_$j\"/>";
		}
		echo "<TR>";
		for($j = 0; $j <=8; $j++) {
			echo "<TD CLASS=\"mini\" ID=\"perc{$level}_$j\">{$perc[$lastupdate][$level][$j]}%";
		}
		echo "\n";
		echo "<TR><TD COLSPAN=\"13\" CLASS=\"dates\">Point mouse at date below to update view";
		if (!$allupdates) echo " (<a href=\"maptrick.php?leagueid=$leagueid&allUpdates=1&action=$action\">click here</a> to show all intermediate timepoints)";
		echo ":<br />Season 29: ";
		$ssn = 30;
		for ($i = 0; $i < count($updates); $i++) {
			echo "<SPAN onmouseover=\"changeimages('$i')\"><B><U>{$updates[$i]}</U></B> </SPAN>";
			if (
				$updates[$i] === "2006-06-05" ||
				$updates[$i] === "2006-11-21" ||
				$updates[$i] === "2007-03-08" ||
				$updates[$i] === "2007-06-25"
			) {
				echo "<br /><br />Season $ssn: ";
				$ssn++;
			}
			#else if ($updates[$i] == "2006-04-something")echo "<BR/><BR/><BR/>";
			else echo " | ";
		}
	}
	echo '</TABLE></DIV>';
} else {

echo '<DIV class="kader">';

$englishleaguenames = getAsocArr("leagues_weekly", "LeagueID", "EnglishName", $dbh);
$regionnames = getAsocArr("regions", "RegionID", "RegionName", $dbh);
$regionleagues = getAsocArr("regions", "RegionID", "LeagueID", $dbh);

echo "<b>Random statistics that I have not yet found a place for</b><BR/>\n<BR/>\n";


echo "Top 20's of:<BR/>\n";
echo "<A NAME=\"bloatedleagues\"/>\n";
echo "<LI>Leagues with most regions</LI>\n";
$bloatedleagues = mysql_query("SELECT DISTINCT LeagueID, count( LeagueID ) AS count FROM regions GROUP BY LeagueID ORDER BY count DESC LIMIT 20",$dbh);
echo "<TABLE BORDER=\"1\">\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD class=\"info\"><B>Number of regions</B></TD></TR>\n";
while ($bloatedleague = mysql_fetch_array($bloatedleagues)) {
	echo "\t<TR><TD CLASS=\"info\">{$englishleaguenames[$bloatedleague['LeagueID']]}</TD><TD CLASS=\"info num\">{$bloatedleague['count']}</TD></TR>";
}
echo "</TABLE><br/><br/>\n";

echo "<A NAME=\"emptyleagues\"/>\n";
echo "<LI>Leagues with least users per region (only counting leagues that exist for at least 5 seasons)</LI>\n";
$emptyleagues = mysql_query("SELECT regions.LeagueID AS LeagueID, count( regions.LeagueID ) AS count, ActiveUsers, (ActiveUsers / count( regions.LeagueID )) AS ratio FROM leagues_weekly, regions WHERE regions.leagueid = leagues_weekly.leagueid and Season >= 5 GROUP BY regions.LeagueID ORDER BY ratio ASC LIMIT 20 ", $dbh);

echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Number of regions</B></TD><TD CLASS=\"info\"><B>Number of active users</B></TD><TD CLASS=\"info\"><B>active users per region</B></TD></TR>\n";
while ($emptyleague = mysql_fetch_array($emptyleagues)) {
	echo "\t<TR><TD CLASS=\"info\">{$englishleaguenames[$emptyleague['LeagueID']]}</TD><TD CLASS=\"info num\">{$emptyleague['count']}</TD><TD CLASS=\"info num\">{$emptyleague['ActiveUsers']}</TD><TD CLASS=\"info num\">{$emptyleague['ratio']}</TD></TR>";
}
echo "</TABLE><br/><br/>\n";

echo "<A NAME=\"monocultureleagues\"/>\n";
echo "<LI>Leagues with most users per region</LI>\n";
$monocultureleagues = mysql_query("SELECT regions.LeagueID AS LeagueID, count( regions.LeagueID ) AS count, ActiveUsers, (ActiveUsers / count( regions.LeagueID )) AS ratio FROM leagues_weekly, regions WHERE regions.leagueid = leagues_weekly.leagueid and ActiveUsers > 100 GROUP BY regions.LeagueID ORDER BY ratio DESC LIMIT 20 ", $dbh);

echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Number of regions</B></TD><TD CLASS=\"info\"><B>Number of active users</B></TD><TD CLASS=\"info\"><B>active users per region</B></TD></TR>\n";
while ($monocultureleague = mysql_fetch_array($monocultureleagues)) {
	echo "\t<TR><TD CLASS=\"info\">{$englishleaguenames[$monocultureleague['LeagueID']]}</TD><TD CLASS=\"info num\">{$monocultureleague['count']}</TD><TD CLASS=\"info num\">{$monocultureleague['ActiveUsers']}</TD><TD CLASS=\"info num\">{$monocultureleague['ratio']}</TD></TR>";
}
echo "</TABLE><br/><br/>\n";

echo "[training stats have been <B>moved to a separate page</B>. Click <A HREF=\"training.php\">here</A> to view what leagues train what sort of training least or most.]<BR/><BR/>";

echo "<A NAME=\"globalusers\"/>\n";
echo "Total number of globally active/waiting users:<BR/>\n";
$globalactive = 0;
$globalusers = mysql_query("SELECT sum(activeusers) as active, sum(waitingusers) as waiting, sum(activeusers)+sum(waitingusers) as total FROM leagues_weekly WHERE leagueid != 255", $dbh);
echo "<TABLE border=1>\n\t<TR><TH>Active Users<TH>Waiting Users<TH>Interested users (active+waiting)\n";
while ($globaluser = mysql_fetch_array($globalusers)) {
        echo "\t<TR><TD>{$globaluser['active']}</TD><TD>{$globaluser['waiting']}</TD><TD>{$globaluser['total']}</TD></TR>\n";
        $globalactive = $globaluser['active'];
}

echo "</TABLE><br /><br />\n";
?>Relative change since...<br />
<table border="1"><?php

$query = mysql_query("SELECT
sum( activeusers ) - sum( Active2 ) g2,
sum( activeusers ) - sum( Active14 ) g14,
sum( activeusers ) - sum( Active56 ) g56,
sum( activeusers ) - sum( Active112 ) g112,
sum( activeusers ) - sum( Active224 ) g224,
sum( activeusers ) - sum( Active336 ) g336
FROM history_n, leagues_weekly
WHERE history_n.leagueid = leagues_weekly.leagueid", $dbh);
$row = mysql_fetch_array($query); ?>
<tr><td>2 days ago<td><?php if ($row['g2'] > 0) echo "+"; echo $row['g2']; ?>
<tr><td>2 weeks ago<td><?php if ($row['g14'] > 0) echo "+"; echo $row['g14']; ?>
<tr><td>8 weeks ago<td><?php if ($row['g56'] > 0) echo "+"; echo $row['g56']; ?>
<tr><td>1 season ago<td><?php if ($row['g112'] > 0) echo "+"; echo $row['g112']; ?>
<tr><td>2 seasons ago<td><?php if ($row['g224'] > 0) echo "+"; echo $row['g224']; ?>
<tr><td>3 seasons ago<td><?php if ($row['g336'] > 0) echo "+"; echo $row['g336']; ?>
</table><br /><br /><?php

echo "History of global number of users (since 7 January 2002):<br/>\n";
echo "<IMG ALT=\"History of number of Active / Total users\" SRC=\"graphs/global.png\"><BR/>\n<BR/>\n";
echo "<IMG ALT=\"History of number of Waiting users\" SRC=\"graphs/global-w.png\"><BR/>\n<BR/>\n";
echo "A page with all graphs of each country is <A HREF=\"allgraphs.php\">here</A>.<BR/>\n<BR/>\n<BR/>\n";

echo "<A NAME=\"successleagues\"/>\n";
echo "<LI>Most successful new leagues (less than 6 seasons)</LI>\n";

$successleagues = mysql_query("SELECT englishname, season, activeusers, waitingusers, ( activeusers + waitingusers ) AS interestedusers, ( ( activeusers + waitingusers ) / season ) AS users_per FROM  `leagues_weekly` WHERE season <6 AND leagueid != 255 ORDER  BY users_per DESC , interestedusers DESC , activeusers DESC , waitingusers DESC LIMIT 20 ", $dbh);
echo "<table border=\"1\">\n\t<tr><th class=\"info\">Country<th class=\"info\">Season<th class=\"info\">active<th class=\"info\">+<th class=\"info\">waiting<th class=\"info\">=<th class=\"info\">interested users<th class=\"info\">Users per season\n";
while ($successleague = mysql_fetch_array($successleagues)) {
	echo "\t<tr><td class=\"info\">{$successleague['englishname']}<td class=\"info num\">{$successleague['season']}<td class=\"info num\">{$successleague['activeusers']}<td class=\"info\">+<td class=\"info num\">{$successleague['waitingusers']}<td class=\"info\">=<td class=\"info num\">{$successleague['interestedusers']}<td class=\"info num\">{$successleague['users_per']}\n";
}
echo "</table><br /><br />\n";

echo "<A NAME=\"successoldleagues\"/>\n";
echo "<LI>Most successful old leagues (6 or more seasons)</LI>\n";

$successoldleagues = mysql_query("SELECT englishname, season, activeusers, waitingusers, ( activeusers + waitingusers ) AS interestedusers, ( ( activeusers + waitingusers ) / season ) AS users_per FROM  `leagues_weekly` WHERE season >=6 AND leagueid != 255 ORDER  BY users_per DESC , interestedusers DESC , activeusers DESC , waitingusers DESC LIMIT 20 ", $dbh);
echo "<table border=\"1\">\n\t<tr><th class=\"info\">Country<th class=\"info\">Season<th class=\"info\">active<th class=\"info\">+<th class=\"info\">waiting<th class=\"info\">=<th class=\"info\">interested users<th class=\"info\">Users per season\n";
while ($successoldleague = mysql_fetch_array($successoldleagues)) {
	echo "\t<tr><td class=\"info\">{$successoldleague['englishname']}<td class=\"info num\">{$successoldleague['season']}<td class=\"info num\">{$successoldleague['activeusers']}<td class=\"info\">+<td class=\"info num\">{$successoldleague['waitingusers']}<td class=\"info\">=<td class=\"info num\">{$successoldleague['interestedusers']}<td class=\"info num\">{$successoldleague['users_per']}\n";
}
echo "</table><br/><br/>\n";

echo "<A NAME=\"hardtogetleagues\"/>\n";
echo "<LI>Leagues that are the hardest to get a flag from</LI>\n";

echo "<BR/><A HREF=\"coolness.php\">[ See the Coolness Rating page ]</A><br/><br/>\n";


echo "<A NAME=\"queueleagues\"/>\n";
echo "<LI>Leagues with the biggest waiting list</LI>\n";

$queueleagues = mysql_query("SELECT LeagueID, NumberOfTeams, WaitingUsers FROM leagues_weekly WHERE leagueid != 255 ORDER BY WaitingUsers DESC LIMIT 20", $dbh);
echo "<table border=1>\n\t<tr><th class=\"info\">Country<th class=\"info\">Capacity<th class=\"info\">Waiting users\n";
while ($queueleague = mysql_fetch_array($queueleagues)) {
	echo "\t<tr><td class=\"info\">{$englishleaguenames[$queueleague['LeagueID']]}<td class=\"info num\">{$queueleague['NumberOfTeams']}<td class=\"info num\">{$queueleague['WaitingUsers']}\n";
}
echo "</table><br/><br/>\n";

echo "<A NAME=\"crowdedleagues\"/>\n";
echo "<LI>Leagues most eagerly awaiting expansion</LI>\n";

$crowdedleagues = mysql_query("SELECT LeagueID, NumberOfTeams, newcumsize, numberoflevels, activeusers, waitingusers, ( activeusers + waitingusers ) AS NumberOfInterested, (CAST((activeusers + waitingusers)-newcumsize AS SIGNED) / CAST(numberofteams-newcumsize AS SIGNED)) *100 AS Percentage, ((activeusers+waitingusers)/numberofteams)*100 as totperc FROM leagues_weekly, divsizes WHERE level = numberoflevels-1 AND leagueid != 255 ORDER BY Percentage DESC LIMIT 20", $dbh);
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Capacity</B></TD><TD CLASS=\"info\"><B>Bottom div. Size</B></TD><TD CLASS=\"info\"><B>Active users +</B></TD><TD CLASS=\"info\"><B>Waiting users = </B></TD><TD CLASS=\"info\"><B>Interested users</B></TD><TD CLASS=\"info\"><B>Total % full</B></TD><TD CLASS=\"info\"><B>Bottom division % full*</B></TD></TR>\n";
while ($crowdedleague = mysql_fetch_array($crowdedleagues)) {
	$bds = $crowdedleague['NumberOfTeams']-$crowdedleague['newcumsize'];
	echo "\t<TR><TD CLASS=\"info\">{$englishleaguenames[$crowdedleague['LeagueID']]}</TD><TD CLASS=\"info num\">{$crowdedleague['NumberOfTeams']}</TD><TD CLASS=\"info num\">$bds</TD><TD CLASS=\"info num\">{$crowdedleague['activeusers']}</TD><TD CLASS=\"info num\">{$crowdedleague['waitingusers']}</TD><TD CLASS=\"info num\">{$crowdedleague['NumberOfInterested']}</TD><TD CLASS=\"info num\">";
	if ($crowdedleague['totperc'] > 100) {
		echo "<FONT COLOR=\"red\"><B>{$crowdedleague['totperc']}</B></FONT>";
	} else {
		echo "{$crowdedleague['totperc']}";
	}
	echo "</TD><TD CLASS=\"info num\">";
	if ($crowdedleague['Percentage'] > 100) {
		echo "<FONT COLOR=\"red\"><B>{$crowdedleague['Percentage']}</B></FONT>";
	} else {
		echo "{$crowdedleague['Percentage']}";
	}
	echo "</TD></TR>\n";
}
echo "</TABLE>*: assuming all higher levels are fully filled; &gt;100% means the waiting list exceeds total capacity<br/><br/><br/>\n";

echo "<A NAME=\"uncrowdedleagues\"/>\n";
echo "<LI>Leagues with plenty of room to grow (only counting leagues that exist for at least 5 seasons)</LI>\n";
$crowdedleagues = mysql_query("SELECT LeagueID, NumberOfTeams, newcumsize, numberoflevels, activeusers, waitingusers, ( activeusers + waitingusers ) AS NumberOfInterested, (((activeusers + waitingusers)-newcumsize) / (numberofteams-newcumsize)) *100 AS Percentage, ((activeusers+waitingusers)/numberofteams)*100 as totperc FROM leagues_weekly, divsizes WHERE level = numberoflevels-1 AND Season > 5 AND leagueid != 255 ORDER BY Percentage ASC LIMIT 20", $dbh);
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Capacity</B></TD><TD CLASS=\"info\"><B>Bottom div. Size</B></TD><TD CLASS=\"info\"><B>Active users +</B></TD><TD CLASS=\"info\"><B>Waiting users = </B></TD><TD CLASS=\"info\"><B>Interested users</B></TD><TD CLASS=\"info\"><B>Total % full</B></TD><TD CLASS=\"info\"><B>Bottom division % full*</B></TD></TR>\n";
while ($crowdedleague = mysql_fetch_array($crowdedleagues)) {
	$bds = $crowdedleague['NumberOfTeams']-$crowdedleague['newcumsize'];
	echo "\t<TR><TD CLASS=\"info\">{$englishleaguenames[$crowdedleague['LeagueID']]}</TD><TD CLASS=\"info num\">{$crowdedleague['NumberOfTeams']}</TD><TD CLASS=\"info num\">$bds</TD><TD CLASS=\"info num\">{$crowdedleague['activeusers']}</TD><TD CLASS=\"info num\">{$crowdedleague['waitingusers']}</TD><TD CLASS=\"info num\">{$crowdedleague['NumberOfInterested']}</TD><TD CLASS=\"info num\">{$crowdedleague['totperc']}</TD><TD CLASS=\"info num\">";
	if ($crowdedleague['Percentage'] < 0) {
		echo "<FONT COLOR=\"blue\"><B>{$crowdedleague['Percentage']}</B></FONT>";
	} else {
		echo "{$crowdedleague['Percentage']}";
	}
	echo "</TD></TR>\n";
}
echo "</TABLE>*: assuming all higher levels are fully filled; a negative percentage means they cannot be, in any case<br/><br/><br/>\n";

echo "<A NAME=\"candidatesperc\"/>\n";
echo "<LI>Countries that are good candidates to be added next season (based on internet coverage %)</LI>\n";
$query = mysql_query("SELECT name, internet_users, population, 100*(internet_users/population) as perc from countries left join tlds on ciatld = id where ciatld IS NULL and internet_users is not null and id != 'ee' and id !='nz' and id != 'rq' and id != 'as' and id != 'mn' and id != 'gl' and id != 'gi' and id != 're' and id != 'yi' and id != 'pp' ORDER BY internet_users/population DESC LIMIT 20", $dbh);
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Internet users</B></TD><TD CLASS=\"info\"><B>Population</B></TD><TD  CLASS=\"info\"><B>Percentage</B></TD></TR>\n";
while ($row = mysql_fetch_array($query)) {
	echo "<TR><TD CLASS=\"info\">{$row['name']}</TD><TD CLASS=\"info num\">" . number_format($row['internet_users']) . "</TD><TD CLASS=\"info num\">" . number_format($row['population']) . "</TD><TD CLASS=\"info num\">{$row['perc']}%</TD></TR>\n";
}

echo "</TABLE>\n<br/><br/>\n\n";

echo "<A NAME=\"candidatesabs\"/>\n";
echo "<LI>Countries that are good candidates to be added next season (based on total number of internet users)</LI>\n";
$query = mysql_query("SELECT name, internet_users, population, 100*(internet_users/population) as perc from countries left join tlds on ciatld = id where ciatld IS NULL and internet_users is not null and id != 'ee' and id !='nz' and id != 'rq' and id != 'as' and id != 'mn' and id != 'gl' and id != 'gi' and id != 're' and id != 'yi' and id != 'pp' ORDER BY internet_users DESC LIMIT 20", $dbh);
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Internet users</B></TD><TD CLASS=\"info\"><B>Population</B></TD><TD CLASS=\"info\"><B>Percentage</B></TD></TR>\n";
while ($row = mysql_fetch_array($query)) {
	echo "<TR><TD CLASS=\"info\">{$row['name']}</TD><TD CLASS=\"info num\">" . number_format($row['internet_users']) . "</TD><TD CLASS=\"info num\">" . number_format($row['population']) . "</TD><TD CLASS=\"info num\">{$row['perc']}%</TD></TR>\n";
}

echo "</TABLE>\n<br/><br/>\n\n";
echo "<A NAME=\"supportingleagues\"/>\n";

$globalsupportersquery = mysql_query("SELECT count(*) num FROM `teams_$lastupdate` WHERE hassupporter = 1", $dbh);
while ($globalsupporter = mysql_fetch_array($globalsupportersquery)) {
	$globalsupporters = $globalsupporter['num'];
}

$globalsupperc = round(($globalsupporters / $globalactive) * 100,2);

if (isset($_GET['heavyload']) && $_GET['heavyload'] > 0) {
	echo "<LI>Leagues with the highest percentage of supporters (on $lastupdate) (globally: $globalsupporters($globalsupperc%))</LI>\n";
	
	$supportingleagues = mysql_query("select englishname , activeusers, count( * ) supporters, 100*(count(*)/activeusers) percentage from leagues_weekly , `teams_$lastupdate` teams where hassupporter = 1 and leagues_weekly . leagueid = teams . leagueid group by teams . leagueid  ORDER BY `percentage` desc", $dbh);

	echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Active Users</B></TD><TD CLASS=\"info\"><B>Supporters (%)</B></TD></TR>\n";
	while ($supportingleague = mysql_fetch_array($supportingleagues)) {
		echo "\t<TR><TD CLASS=\"info\">{$supportingleague['englishname']}</TD><TD CLASS=\"info num\">{$supportingleague['activeusers']}</TD><TD CLASS=\"info num\">{$supportingleague['supporters']} ({$supportingleague['percentage']}%)</TD></TR>\n";
	}
	echo "</TABLE><br/><br/>\n\n";
} else {
	echo "Stats about <a href=\"maptrick.php?heavyload=1#supportingleagues\">leagues with the highest percentage of supporters</a> have been moved to a separate page due to excessive server load.<br /><br />\n\n";
}
echo "<A NAME=\"bigregions\"/>\n";
echo "<LI>Regions with most teams (incl. bots)</LI>\n";
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Region</B></TD><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Number of users</B></TD></TR>\n";
$bigregions = mysql_query("SELECT * FROM `regions` ORDER BY NumberOfUsers DESC LIMIT 20", $dbh);
while ($bigregion = mysql_fetch_array($bigregions)) {
	echo "\t<TR><TD CLASS=\"info\">{$regionnames[$bigregion['RegionID']]}</TD><TD CLASS=\"info\">{$englishleaguenames[$regionleagues[$bigregion['RegionID']]]}</TD><TD CLASS=\"info num\">{$bigregion['NumberOfUsers']}</TD></TR>";
}
echo "</TABLE><br/><br/>\n";

echo "<A NAME=\"busyregions\"/>\n";
echo "<LI>Regions with highest percentage of teams (incl. bots) in a league</LI>\n";
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Region</B></TD><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Teams</B></TD><TD CLASS=\"info\"><B>% of country</B></TD></TR>\n";
$busyregions = mysql_query("SELECT regionname, EnglishName, leagues_weekly.leagueid AS LeagueID, regions.numberofusers AS RegionUsers, ( regions.numberofusers / leagues_weekly.numberofteams ) *100 AS LeaguePercentage FROM regions, leagues_weekly WHERE regions.leagueid = leagues_weekly.leagueid ORDER  BY LeaguePercentage DESC LIMIT 20", $dbh);
while ($busyregion = mysql_fetch_array($busyregions)) {
	echo "\t<TR><TD CLASS=\"info\">{$busyregion['regionname']}</TD><TD CLASS=\"info\">{$busyregion['EnglishName']}</TD><TD CLASS=\"info num\">{$busyregion['RegionUsers']}</TD><TD CLASS=\"info num\">{$busyregion['LeaguePercentage']}</TD></TR>";
}
echo "</TABLE><br/><br/>\n";

$query = mysql_query("SELECT (sum(activeusers)+sum(waitingusers))interested, sum(internet_users) internet, sum(population) total, sum(total_area) surface from leagues_weekly, countries, tlds where leagues_weekly.leagueid = tlds.leagueid and tlds.ciatld = countries.id", $dbh);

while ($row = mysql_fetch_array($query)) {
	$globalinterested = $row['interested'];
	$globalinternet = $row['internet'];
	$globalpeople = $row['total'];
	$globalsurface = $row['surface'];
}

$query = mysql_query("SELECT sum(internet_users) internet, sum(population) people, sum(total_area) surface from countries", $dbh);

while ($row = mysql_fetch_array($query)) {
	$worldinternet = $row['internet'];
	$worldpeople = $row['people'];
	$worldsurface = $row['surface'];
}

$globalpart = round($globalpeople / $globalinterested, 0);
$worldpart = round($worldpeople / $globalinterested, 0);

echo "<A NAME=\"poppart\"/>\n";
echo "<LI>Proportion of country population playing Hattrick<BR/>(All HT countries: " . number_format($globalinterested) . " interested users / " . number_format($globalpeople) . " people / 1:$globalpart)<BR/>(World: " . number_format($worldpeople) . " people / 1:$worldpart)</LI>\n";
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Interested users</B></TD><TD CLASS=\"info\"><B>Population</B></TD><TD CLASS=\"info\"><B>Ratio</B></TD></TR>\n";
$query = mysql_query("SELECT englishname , ( activeusers + waitingusers ) interested , name , internet_users , internet_users / ( activeusers + waitingusers ) internet , population , population / ( activeusers + waitingusers ) total FROM tlds , countries , leagues_weekly WHERE tlds . ciatld = countries . id and tlds . leagueid = leagues_weekly . leagueid and (activeusers + waitingusers ) > 0 ORDER BY total ASC", $dbh);
$i = 1;
while ($row = mysql_fetch_array($query)) {
	echo "\t<TR><TD CLASS=\"info\">$i. {$row['englishname']}</TD><TD CLASS=\"info num\">{$row['interested']}</TD><TD CLASS=\"info num\">" . number_format($row['population']) . "</TD><TD CLASS=\"info num\">1:" . round($row['total'],0) . "</TD></TR>";
	$i++;
}
echo "</TABLE><br/><br/>\n";

$globalpart = round($globalinternet / $globalinterested, 0);
$worldpart = round($worldinternet / $globalinterested, 0);

echo "<A NAME=\"netpart\"/>\n";
echo "<LI>Proportion of internet users playing Hattrick<BR/>(All HT countries: " . number_format($globalinterested) . " interested users / " . number_format($globalinternet) . " internet users / 1:$globalpart)<BR/>(World: " . number_format($worldinternet) . " internet users / 1:$worldpart)</LI>\n";
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Interested users</B></TD><TD CLASS=\"info\"><B>Internet users</B></TD><TD CLASS=\"info\"><B>Ratio</B></TD></TR>\n";
$query = mysql_query("SELECT englishname , ( activeusers + waitingusers ) interested , name , internet_users , internet_users / ( activeusers + waitingusers ) internet , population , population / ( activeusers + waitingusers ) total FROM tlds , countries , leagues_weekly WHERE tlds . ciatld = countries . id and tlds . leagueid = leagues_weekly . leagueid  and (activeusers + waitingusers ) > 0 ORDER BY internet ASC", $dbh);
$i = 1;
while ($row = mysql_fetch_array($query)) {
        echo "\t<TR><TD CLASS=\"info\">$i. {$row['englishname']}</TD><TD CLASS=\"info num\">{$row['interested']}</TD><TD CLASS=\"info num\">" . number_format($row['internet_users']) . "</TD><TD CLASS=\"info num\">1:" . round($row['internet'],0) . "</TD></TR>";
	$i++;
}
echo "</TABLE><br/><br/>\n";

$globalpart = round($globalsurface / $globalinterested, 0);
$worldpart = round($worldsurface / $globalinterested, 0);

echo "<A NAME=\"surfacepart\"/>\n";
echo "<LI>Hattrick players per km<sup>2</sup><BR/>(All HT countries: " . number_format($globalinterested) . " interested users / " . number_format($globalsurface) . " km<sup>2</sup> / 1:$globalpart)<br />(World: " . number_format($worldsurface) . " km<sup>2</sup> / 1:$worldpart)</LI>\n";
echo "<table border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Interested users</B></TD><TD CLASS=\"info\"><B>Total area (km<sup>2</sup>)</B></TD><TD CLASS=\"info\"><B>Ratio</B></TD></TR>\n";
$query = mysql_query("SELECT englishname , ( activeusers + waitingusers ) interested , name , total_area , total_area / ( activeusers + waitingusers ) land , population , population / ( activeusers + waitingusers ) total FROM tlds , countries , leagues_weekly WHERE tlds . ciatld = countries . id and tlds . leagueid = leagues_weekly . leagueid  and (activeusers + waitingusers ) > 0 ORDER BY land ASC", $dbh);
$i = 1;
while ($row = mysql_fetch_array($query)) {
        echo "\t<TR><TD CLASS=\"info\">$i. {$row['englishname']}</TD><TD CLASS=\"info num\">{$row['interested']}</TD><TD CLASS=\"info num\">" . number_format($row['total_area']) . "</TD><TD CLASS=\"info num\">1:" . round($row['land'],1) . "</TD></TR>";
	$i++;
}
echo "</TABLE><br/><br/>\n";


echo "<A NAME=\"biggrowth\"/>\n";
echo "<LI>Countries with the highest percentual growth since 2 days ago</LI>\n";
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Active users</B></TD><TD CLASS=\"info\"><B>Difference with 2 days ago (%)</B></TD></TR>\n";
$query = mysql_query("SELECT
leagues_weekly.leagueid leagueid, shortname, activeusers, 
CAST(activeusers-active2 AS SIGNED) d2, round((CAST(activeusers-active2 AS SIGNED) / active2)*100,2) p2 
FROM leagues_weekly, history_n
WHERE leagues_weekly.leagueid = history_n.leagueid
AND CAST(activeusers-active2 AS SIGNED) > 0
AND leagues_weekly.leagueid != 255
ORDER BY p2 DESC, d2 DESC LIMIT 20", $dbh);
while ($row = mysql_fetch_array($query)) {
	echo "\t<TR><TD CLASS=\"info\"><A HREF=\"maptrick.php?leagueid={$row['leagueid']}\">{$row['shortname']}</A></TD><TD CLASS=\"info num\">{$row['activeusers']}</TD><TD CLASS=\"info num\">{$row['d2']} ({$row['p2']}%)</TD></TR>\n";
}
echo "</TABLE><br/><br/>\n";

echo "<A NAME=\"bigloss\"/>\n";
echo "<LI>Countries with the highest percentual shrinkage since 2 days ago</LI>\n";
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Active users</B></TD><TD CLASS=\"info\"><B>Difference with 2 days ago (%)</B></TD></TR>\n";
$query = mysql_query("SELECT
leagues_weekly.leagueid leagueid, shortname, activeusers,
CAST(activeusers-active2 AS SIGNED) d2, round((CAST(activeusers-active2 AS SIGNED) / active2)*100,2) p2
FROM leagues_weekly, history_n
WHERE leagues_weekly.leagueid = history_n.leagueid 
AND CAST(activeusers-active2 AS SIGNED) < 0 
AND leagues_weekly.leagueid != 255
ORDER BY p2 ASC, d2 ASC LIMIT 20", $dbh);
while ($row = mysql_fetch_array($query)) {
	echo "\t<TR><TD CLASS=\"info\"><A HREF=\"maptrick.php?leagueid={$row['leagueid']}\">{$row['shortname']}</A></TD><TD CLASS=\"info num\">{$row['activeusers']}</TD><TD CLASS=\"info num\">{$row['d2']} ({$row['p2']}%)</TD></TR>\n";
}
echo "</TABLE><br/><br/>\n";

echo "<A NAME=\"bigabsgrowth\"/>\n";
echo "<LI>Countries with the highest absolute growth since 2 days ago</LI>\n";
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Active users</B></TD><TD CLASS=\"info\"><B>Difference with 2 days ago (%)</B></TD></TR>\n";
$query = mysql_query("SELECT
leagues_weekly.leagueid leagueid, shortname, activeusers,
CAST(activeusers-active2 AS SIGNED) d2, round((CAST(activeusers-active2 AS SIGNED) / active2)*100,2) p2
FROM leagues_weekly, history_n
WHERE leagues_weekly.leagueid = history_n.leagueid
AND CAST(activeusers-active2 AS SIGNED) > 0
AND leagues_weekly.leagueid != 255
ORDER BY d2 DESC, p2 DESC LIMIT 20", $dbh);
while ($row = mysql_fetch_array($query)) {
	echo "\t<TR><TD CLASS=\"info\"><A HREF=\"maptrick.php?leagueid={$row['leagueid']}\">{$row['shortname']}</A></TD><TD CLASS=\"info num\">{$row['activeusers']}</TD><TD CLASS=\"info num\">{$row['d2']} ({$row['p2']}%)</TD></TR>\n";
}
echo "</TABLE><br/><br/>\n";

echo "<A NAME=\"bigabsloss\"/>\n";
echo "<LI>Countries with the highest absolute shrinkage since 2 days ago</LI>\n";
echo "<TABLE border=1>\n\t<TR><TD CLASS=\"info\"><B>Country</B></TD><TD CLASS=\"info\"><B>Active users</B></TD><TD CLASS=\"info\"><B>Difference with 2 days ago (%)</B></TD></TR>\n";
$query = mysql_query("SELECT
leagues_weekly.leagueid leagueid, shortname, activeusers,
CAST(activeusers-active2 AS SIGNED) d2, round((CAST(activeusers-active2 AS SIGNED) / active2)*100,2) p2
FROM leagues_weekly, history_n
WHERE leagues_weekly.leagueid = history_n.leagueid
AND CAST(activeusers-active2 AS SIGNED) < 0 
AND leagues_weekly.leagueid != 255
ORDER BY d2 ASC, p2 ASC LIMIT 20", $dbh);
while ($row = mysql_fetch_array($query)) {
	echo "\t<TR><TD CLASS=\"info\"><A HREF=\"maptrick.php?leagueid={$row['leagueid']}\">{$row['shortname']}</A></TD><TD CLASS=\"info num\">{$row['activeusers']}</TD><TD CLASS=\"info num\">{$row['d2']} ({$row['p2']}%)</TD></TR>\n";
}
echo "</TABLE><br/><br/>\n";

echo '</DIV><BR/>';
}
mysql_close($dbh);

display_ad("maptrick_bottom");
urchin();
?>
</BODY>
</HTML>
